"""
cron: 0 */1 * * *
new Env('天气数据采集');
"""
import requests
import os
import mysql.connector
from datetime import datetime

def get_db_config():
    """获取数据库配置"""
    try:
        host = os.environ.get('HOST')
        user = os.environ.get('USER')
        password = os.environ.get('PASSWORD')
        database = os.environ.get('DATABASE')
        
        print("正在获取环境变量...")
        print(f"数据库地址：{host}")
        print(f"数据库用户名：{user}")
        print(f"数据库密码：{password}")
        print(f"数据库名称：{database}")
        
        # 获取和风天气API密钥
        weather_key = os.environ.get('QWEATHER_KEY')
        print(f"天气API密钥：{weather_key}")
        
        if not all([host, user, password, database, weather_key]):
            raise ValueError("环境变量不完整")
            
        return host, user, password, database, weather_key
    except Exception as e:
        print(f"获取配置失败: {str(e)}")
        raise

def get_city_weather(api_key, city_name):
    """获取城市天气数据"""
    try:
        # 1. 先获取城市ID
        location_url = "https://geoapi.qweather.com/v2/city/lookup"
        params = {
            'location': city_name,
            'key': api_key
        }
        
        print(f"正在获取{city_name}的城市ID...")
        response = requests.get(location_url, params=params)
        response.raise_for_status()
        
        location_data = response.json()
        if location_data['code'] != '200' or not location_data['location']:
            raise ValueError(f"未找到城市: {city_name}")
            
        city_id = location_data['location'][0]['id']
        
        # 2. 获取实时天气
        weather_url = "https://api.qweather.com/v7/weather/now"
        params = {
            'location': city_id,
            'key': api_key
        }
        
        print(f"正在获取{city_name}的天气数据...")
        response = requests.get(weather_url, params=params)
        response.raise_for_status()
        
        weather_data = response.json()
        if weather_data['code'] != '200':
            raise ValueError(f"获取天气数据失败: {weather_data['code']}")
            
        return {
            'city': city_name,
            'temperature': weather_data['now']['temp'],
            'weather': weather_data['now']['text'],
            'humidity': weather_data['now']['humidity'],
            'wind_direction': weather_data['now']['windDir'],
            'wind_speed': weather_data['now']['windScale'],
            'create_time': datetime.now()
        }
        
    except Exception as e:
        print(f"获取天气数据失败: {str(e)}")
        return None

def save_to_database(connection, weather_data):
    """保存天气数据到数据库"""
    try:
        cursor = connection.cursor()
        
        # 删除该城市的旧数据
        print(f"删除{weather_data['city']}的旧数据...")
        delete_query = "DELETE FROM ningm_weather WHERE city = %s"
        cursor.execute(delete_query, (weather_data['city'],))
        
        # 插入新数据
        print(f"保存{weather_data['city']}的天气数据...")
        insert_query = """
            INSERT INTO ningm_weather 
            (city, temperature, weather, humidity, wind_direction, wind_speed, create_time) 
            VALUES (%s, %s, %s, %s, %s, %s, %s)
        """
        insert_values = (
            weather_data['city'],
            weather_data['temperature'],
            weather_data['weather'],
            weather_data['humidity'],
            weather_data['wind_direction'],
            weather_data['wind_speed'],
            weather_data['create_time']
        )
        cursor.execute(insert_query, insert_values)
        
        connection.commit()
        print(f"成功保存{weather_data['city']}的天气数据")
        
    except Exception as e:
        print(f"保存天气数据失败: {str(e)}")
        connection.rollback()
        raise
    finally:
        cursor.close()

def main():
    connection = None
    try:
        # 获取配置
        host, user, password, database, weather_key = get_db_config()
        
        # 连接数据库
        print("连接到MySQL数据库...")
        connection = mysql.connector.connect(
            host=host,
            user=user,
            password=password,
            database=database
        )
        
        # 需要获取天气的城市列表
        cities = ['北京', '上海', '广州', '深圳']
        
        # 获取并保存每个城市的天气
        for city in cities:
            weather_data = get_city_weather(weather_key, city)
            if weather_data:
                save_to_database(connection, weather_data)
        
        print("天气数据采集任务完成！")
        
    except Exception as e:
        print(f"程序执行失败: {str(e)}")
    finally:
        if connection:
            connection.close()
            print("数据库连接已关闭")

if __name__ == "__main__":
    main() 